package com.xpjhs.util;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.ibm.icu.text.NumberFormat;

//Excel源代码，ExcelRead.java导入类 
@SuppressWarnings({ "rawtypes", "unchecked" })
public class ExcelReadUtil {

	private Workbook workbook;// 工作簿

	/*
	 * 读取文件路径字符串
	 */

	public void importExcel(InputStream strfile) throws IOException {
		try {
			workbook=WorkbookFactory.create(new File("f:/2.xlsx"));
		} catch (Exception e) {
		  workbook=new XSSFWorkbook(strfile);
		}
	}


	/*
	 * 从第几张工作表第几行的数据importExcel导入
	 */
	public List readRow(int sheetNumber, int rowIndex) {
		List result = new ArrayList();
		// 获得指定的sheet
		Sheet sheet = workbook.getSheetAt(sheetNumber);
		// 获得sheet总行数
		int rowCount = sheet.getLastRowNum();
		if (rowCount < 1) {
			return result;
		}
		// 获得行对象
		Row row = sheet.getRow(rowIndex);
		if (null != row) {
			// 获得本行中单元格的个数
			int cellCount = row.getLastCellNum();
			// 遍历列cell
			for (short cellIndex = 0; cellIndex < cellCount; cellIndex++) {
				Cell cell = row.getCell(cellIndex);
				// 获得指定单元格中的数据
				Object cellStr = this.getCellString(cell);
				result.add(cellStr);
			}
		}

		return result;
	}

	/**
	 * 获取指定工作表的总
	 * 
	 * @param sheetNumber
	 * @return
	 */
	public int getRowIndex(int sheetNumber) {
		Sheet sheet = workbook.getSheetAt(sheetNumber);
		// 获得sheet总行数

		int rowCount = sheet.getPhysicalNumberOfRows();
		if (rowCount < 1) {
			return 0;
		}
		return rowCount;
	}

	/**
	 * 获取一个cell的数据类型
	 * 
	 * @param cell
	 * @return
	 */
	@SuppressWarnings("unused")
	private Object getCellString_(Cell cell) {
		SimpleDateFormat  sdf=new SimpleDateFormat("yyyy年MM月dd日");
		Object result = null;
		if (cell != null) {
			// 单元格类型：Numeric:0,String:1,Formula:2,Blank:3,Boolean:4,Error:5
			int cellType = cell.getCellType();
			switch (cellType) {
			case Cell.CELL_TYPE_STRING:
				result = cell.getRichStringCellValue().getString();
				break;
			case Cell.CELL_TYPE_NUMERIC:
				if (DateUtil.isCellDateFormatted(cell)) {
					result = sdf.format(cell.getDateCellValue());
				} else {
					result = cell.getNumericCellValue();
				}
				break;
			case Cell.CELL_TYPE_FORMULA:
				result = cell.getNumericCellValue();
				break;
			case Cell.CELL_TYPE_BOOLEAN:
				result = cell.getBooleanCellValue();
				break;
			case Cell.CELL_TYPE_BLANK:
				result = null;
				break;
			case Cell.CELL_TYPE_ERROR:
				result = null;
				break;
			default:
				System.out.println("枚举了所有类型");
				break;
			}
		}
		return result;
	}

	/**
	 * 获取一个cell全部是String
	 * 
	 * @param cell
	 * @return
	 */
	private Object getCellString(Cell cell) {
		SimpleDateFormat  sdf=new SimpleDateFormat("yyyy年MM月dd日");
		Object result = null;
		if (cell != null) {
			// 单元格类型：Numeric:0,String:1,Formula:2,Blank:3,Boolean:4,Error:5
			int cellType = cell.getCellType();
			switch (cellType) {
			case Cell.CELL_TYPE_STRING:
				result = cell.getRichStringCellValue().getString();
				break;
			case Cell.CELL_TYPE_NUMERIC:
				if (DateUtil.isCellDateFormatted(cell)) {
					result = cell.getDateCellValue();
				} else {
					NumberFormat formate = NumberFormat.getInstance();
					formate.setMaximumFractionDigits(0);
					result = formate.format(cell.getNumericCellValue());
				}
				break;
			case Cell.CELL_TYPE_FORMULA:
				result = cell.getNumericCellValue();
				break;
			case Cell.CELL_TYPE_BOOLEAN:
				result = cell.getBooleanCellValue();
				break;
			case Cell.CELL_TYPE_BLANK:
				result = null;
				break;
			case Cell.CELL_TYPE_ERROR:
				result = null;
				break;
			default:
				System.out.println("枚举了所有类型");
				break;
			}
		}
		return result;
	}
}
